package servlet;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;


import util.ExcelUtil;
import util.JDBCUtil;
import util.NumberUtil;
import util.POIUtil;

/**
 * Servlet implementation class ExcelHandleServlet
 */
@WebServlet("/ExcelHandleServlet")
public class ExcelHandleServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     *	 处理上传的excel文件，并合并成同一个excel，且生成信息对不上的错误excel
     * @see HttpServlet#HttpServlet()
     */
    public ExcelHandleServlet() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doPost(request,response);
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		response.setHeader("Content-type", "text/html;charset=UTF-8");
		response.setCharacterEncoding("utf-8");
		String url =System.getProperty("user.dir")+"/abc";
		double midterm =  Double.parseDouble(request.getParameter("midterm"))/100;  //期中考权值
		double terminal = Double.parseDouble(request.getParameter("terminal"))/100;	//期末考权值
		double pta = Double.parseDouble(request.getParameter("pta"))/100;  //pta权值
		double MOOC = Double.parseDouble(request.getParameter("MOOC"))/100; //MOOC权值
		int midScore=0,terScore=0,ptaScore=0,MOOCScore=0,finalScore=0;
		double score;
		ArrayList<ArrayList<Object>> ans = new ArrayList<ArrayList<Object>>();
		ArrayList<Object> cowList;
		File file = new File(url+"/java网络汇总.xlsx");   //如果汇总存在 就不再生成汇总
		if(!file.exists())   POIUtil.mergeExcel(url);
		ArrayList<ArrayList<Object>> result = ExcelUtil.readExcel(new File(url+"/java网络汇总.xlsx"));   //读取汇总
		cowList = new ArrayList<Object>();
		for(int i = 0; i < result.get(0).size(); i++) {
			cowList.add(result.get(0).get(i));
		}
		ans.add(cowList);
		Connection con = null;
		PreparedStatement pStatement = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{
			con = JDBCUtil.getConnection();
			stmt = con.createStatement();
			con.setAutoCommit(false);
		for(int i = 1; i < result.size(); i++) {
			cowList = new ArrayList<Object>();
			finalScore=0;
			score = 0;
			midScore=0;
			terScore=0;
			ptaScore=0;
			MOOCScore=0;
			for(int j = 0; j < result.get(i).size()-1; j++) {
				cowList.add(result.get(i).get(j));
				if(j>=6&&j<10) {
					if(!NumberUtil.isNumber(result.get(i).get(j).toString())||result.get(i).get(j).equals("缺考")||result.get(i).get(j).equals("")) {
						score=0;
					}
					else score = Double.parseDouble(result.get(i).get(j).toString());
				}
				switch(j){
					case 6:
						midScore = (int) (score*midterm);
						break;
					case 7:
						terScore = (int) (score*terminal);
						break;
					case 8:
						ptaScore = (int) (score/326*100*pta);
						break;
					case 9:
						MOOCScore = (int) (score/238*100*MOOC);
						break;
					default:
						break;
				}
			}
			finalScore = midScore+terScore+ptaScore+MOOCScore;  //连接数据库 计算权值后获得总分
			String sql = "update study set finalScore = (?) where id = "+cowList.get(0).toString();
			pStatement = con.prepareStatement(sql);
			pStatement.setString(1, String.valueOf(finalScore));
			pStatement.addBatch();
			pStatement.executeBatch();
			con.commit();
			cowList.add(finalScore);
			ans.add(cowList);
		}
		ExcelUtil.writeExcel(ans, url+"/java网络汇总.xlsx");  //写成java网络汇总.xlsx这个文件
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.realeaseAll(rs, stmt, con, pStatement);
		}
		request.getRequestDispatcher("ShowAllStudent.jsp").forward(request, response);
	}
}
